import {Request,Response} from 'express';
import { verbose } from 'sqlite3'

type Result = {
    code:number, // 返回code  200 或者 402 错误  403 数据库操作错误
    data?: object, // 返回数据
    msg?: string // 错误信息
}
interface HttpData {
    [key: string]:any;
}
class Controller
{
    req?:Request;
    res?:Response;
    HD:HttpData = {}
    DB:any;
    dbname:string = "onsite.db";
    result:Result = {
        code: 200
    }



    constructor(req?:Request,res?:Response) {
        if(req && res) {
            this.req = req;
            this.res = res;
            this.HD = {...req.body.data,...req.query};
        }
        const sqlite = verbose();
        this.DB = new sqlite.Database(this.dbname)
    }

    
    /**
     * 返回数据
     * @param code 
     * @param data 
     * @param msg 
     */
    response(code:number = 200,data?:object,msg?:string) {
        this.DB.close()
        this.result = {
            code,
            data,
            msg
        }
        if(this.res) this.res.json(this.result)
    }

    /**
     * 写入数据
     * @param table 
     * @param data 
     * @returns 
     */
    insert(table:string,data:object) {
        let field = Object.keys(data)
        let values = field.map(() => '?').join(',')
        let bind = Object.values(data)
        return new Promise((resolve,reject) => {
            let sql = `INSERT INTO ${table} (${field.join(',')}) VALUES (${values});`;
            this.DB.run(sql,bind,(error:any) => {
                if(error) {
                    reject({code:403,message:"写入数据失败"})
                } else {
                    this.DB.get(`select rowid from ${table} ORDER BY id DESC`,[],(err:any,row:any) => {
                        resolve(row.id)
                    });
                }
            })
        })
    }

    /**
     * 获取一条数据
     * @param table 
     * @param where 
     * @returns 
     */
    first(table:string,where?:object) {
        let where_string = "";
        let bind:Array<any> = [];
        if(where) {
            where_string = " WHERE " + Object.keys(where).map((item) => `${item} = ?`).join(" AND ");
            bind = Object.values(where);
        }
        return new Promise((resolve,reject) => {
            let sql = `SELECT * FROM ${table}${where_string}`;
            this.DB.get(sql,bind,(err:any,row:any) => {
                if(err) {
                    reject({code:403,message:"数据库查询失败"})
                } else {
                    resolve(row ? row : false)
                }
            })
        })
    }

    /**
     * 获取数据列表
     */
    all(table:string,where?:object) :any {
        let where_string = "";
        let bind:any = [];
        if(where) {
            where_string = " WHERE " + Object.keys(where).map((item) => `${item} = ?`).join(" AND ");
            bind = Object.values(where);
        }
        return new Promise((resolve,reject) => {
            let sql = `SELECT * FROM ${table} ${where_string}`;
            this.DB.all(sql,bind,(err:any,row:any) => {
                if(err) {
                    reject({code:403,message:"数据库查询失败"})
                } else {
                    resolve(row ? row : false)
                }
            })
        })
    }

    /**
     * 修改sql数据
     */
    update(table:String,where:object,data:object) {
        let where_string = "";
        let where_bind:Array <any> = [];
        let set = "";
        let set_bind:Array<any> = [];
        let bind:Array<any> = [];
        if(data) {
            set = " SET " + Object.keys(data).map((item)=>`${item} = ?`).join(",");
            set_bind = Object.values(data);
        }
        if(where) {
            where_string = " WHERE " + Object.keys(where).map((item) => `${item} = ?`).join(" AND ");
            where_bind = Object.values(where);
        }
        bind = set_bind.concat(where_bind);
        let sql = `UPDATE ${table}${set}${where_string}`;
        return new Promise((resolve,reject) => {
            this.DB.run(sql,bind,function(error:any) {
                if(error) {
                    throw new Error(error)
                } else {
                    resolve(true);
                }
            })
        })
    }

    /**
     * 删除数据
     */
    delete(table:String,where:object) {
        let where_string = "";
        let where_bind:Array <any> = [];
        let set_bind:Array<any> = [];
        let bind:Array<any> = [];
        if(where) {
            where_string = " WHERE " + Object.keys(where).map((item) => `${item} = ?`).join(" AND ");
            where_bind = Object.values(where);
        }
        bind = set_bind.concat(where_bind);
        let sql = `DELETE FROM ${table}${where_string}`;
        return new Promise((resolve,reject) => {
            this.DB.run(sql,bind,function(error:any) {
                if(error) {
                    throw new Error(error)
                } else {
                    resolve(true);
                }
            })
        })
    }

    /**
     * 开启事务
     */
    begin() {
        return new Promise((resolve,reject) => {
            this.DB.run("BEGIN TRANSACTION",(error:any) => {
                if(error) {
                    reject(error)
                } else {
                    resolve(true)
                }
            })
        })
    }

    /**
     * 提交事务
     */
    commit() {
        return new Promise((resolve,reject) => {
            if(this.DB.inTransaction) {
                this.DB.run("COMMIT",(error:any) => {
                    if(error) {
                        reject(error)
                    } else {
                        resolve(true)
                    }
                })
            } else {
                resolve(true);
            }
        })
    }
    
    /**
     * 回滚事务
     */
    rollback() {
        return new Promise((resolve,reject) => {
            if(this.DB.inTransaction) {
                this.DB.run("ROLLBACK",(error:any) => {
                    if(error) {
                        reject(error)
                    } else {
                        resolve(true)
                    }
                })
            } else {
                resolve(true)
            }
        })
    }
}

export default Controller